package com.anhuanjia.tools.eao.util;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import com.anhuanjia.tools.eao.beanvo.EaoEngineerVo;
import com.efastsoft.framework.data.Q;
import com.efastsoft.framework.data.Transaction;
import com.efastsoft.framework.utility.StringUtil;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 更新工程师表工具类
 * 
 * @author
 *
 */
public class EngineerUtil {
	// 记录类的输出信息
	static Log log = LogFactory.getLog(EngineerUtil.class);
	// 获取Excel文档的路径
	/*public static String filePath = "D://环评/环境影响评价工程师数据库.xls";

	public static void main(String[] args) {
		
		EngineerUtil as = new EngineerUtil();
		try {
			as.insertEngineer(filePath);
		} catch (ParseException e) {
			e.printStackTrace();
		}
	}*/
	

	public static String insertEngineer(String filePath) throws ParseException {
		int count = 0;// 记录总数
		String oneJoint = "";
		EaoEngineerVo empEmployeeLeave = null;
		List<String> list = new ArrayList<String>();
		Transaction trans = new Transaction();
		try {
			// 创建对Excel工作簿文件的引用
			HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(filePath));
			// 获取工作表
			Sheet sheet = wb.getSheetAt(0);
			// 获取sheet的最后一行
			int lastRow = sheet.getLastRowNum();
			for (int i = 1; i <= lastRow; i++) {
				Row row = sheet.getRow(i);
				int lastCol = row.getLastCellNum();
				empEmployeeLeave = new EaoEngineerVo();
				for (int j = 0; j < lastCol; j++) {
					Cell cell = row.getCell(j);
					String value = "";
					if (cell != null) {
						switch (cell.getCellType()) {
						case Cell.CELL_TYPE_BOOLEAN:// 布尔类型
							value = String.valueOf(cell.getBooleanCellValue());
							break;
						case Cell.CELL_TYPE_NUMERIC:// 数字类型
							if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
								SimpleDateFormat sdf = null;
								if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
									sdf = new SimpleDateFormat("HH:mm");
								} else {// 日期
									sdf = new SimpleDateFormat("yyyy/MM/dd");
								}
								Date date = cell.getDateCellValue();
								value = sdf.format(date);
							} else if (cell.getCellStyle().getDataFormat() == 58) {
								// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
								SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
								double a = cell.getNumericCellValue();
								Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(a);
								value = sdf.format(date);
							} else {
								double a = cell.getNumericCellValue();
								CellStyle style = cell.getCellStyle();
								DecimalFormat format = new DecimalFormat();
								String temp = style.getDataFormatString();
								// 单元格设置成常规
								if (temp.equals("General")) {
									format.applyPattern("#");
								}
								value = format.format(a);
							}
							break;
						case Cell.CELL_TYPE_STRING:// String类型
							value = cell.getStringCellValue();
							break;
						case HSSFCell.CELL_TYPE_BLANK:
							value = "";
						default:
							value = "";
							break;
						}
						if (j == 0) {
							empEmployeeLeave.setEngineerId((long) Integer.parseInt(value.trim()));
							;
						}
						if (j == 1) {
							empEmployeeLeave.setUserName(value.trim());
							;
						}
						if (j == 2) {
							empEmployeeLeave.setApplicant(value.trim());
							;
						}
						if (j == 3) {
							empEmployeeLeave.setRegNumber(value.trim());
							;
						}
						if (j == 4) {
							String professionalNumber = "";
							if (value.trim().replaceAll(",", "").length() == 1) {
								professionalNumber = "000000" + value.trim().replaceAll(",", "");
							} else if (value.trim().replaceAll(",", "").length() == 2) {
								professionalNumber = "00000" + value.trim().replaceAll(",", "");
							} else if (value.trim().replaceAll(",", "").length() == 3) {
								professionalNumber = "0000" + value.trim().replaceAll(",", "");
							} else if (value.trim().replaceAll(",", "").length() == 4) {
								professionalNumber = "000" + value.trim().replaceAll(",", "");
							} else if (value.trim().replaceAll(",", "").length() == 5) {
								professionalNumber = "00" + value.trim().replaceAll(",", "");
							} else if (value.trim().replaceAll(",", "").length() == 6) {
								professionalNumber = "0" + value.trim().replaceAll(",", "");
							} else if (value.trim().replaceAll(",", "").length() == 7) {
								professionalNumber = value.trim().replaceAll(",", "");
							} else if (StringUtil.isNotEmpty(value.trim())) {
								professionalNumber = value.trim().replaceAll(",", "");
							}
							empEmployeeLeave.setProfessionalNumber(professionalNumber);
						}
						if (j == 5) {
							empEmployeeLeave.setRegisterClass(value.trim());
							;
						}
						if (j == 6) {
							empEmployeeLeave.setStartTime(new Date(value.trim()));

						}
						if (j == 7) {
							empEmployeeLeave.setEndTime(new Date(value.trim()));
						}
						if (j == 8) {
							empEmployeeLeave.setProvince(value.trim());

						}
						if (j == 9) {
							if (StringUtil.isNotEmpty(value.trim())) {
								empEmployeeLeave.setIntegrityMessage(value.trim());
							} else {
								empEmployeeLeave.setIntegrityMessage("");
							}
						}
					}
				}
				count++;
				String ternaryOperator = StringUtil.isNotEmpty(empEmployeeLeave.getIntegrityMessage()) ? empEmployeeLeave.getIntegrityMessage() : "'',";
				oneJoint = "'" + empEmployeeLeave.getUserName() + "','" + empEmployeeLeave.getApplicant() + "','"
						+ empEmployeeLeave.getRegNumber() + "','" + empEmployeeLeave.getProfessionalNumber() + "','"
						+ empEmployeeLeave.getRegisterClass() + "','" + strToDate(empEmployeeLeave.getStartTime())
						+ "','" + strToDate(empEmployeeLeave.getEndTime()) + "','" + ternaryOperator + "'";
				list.add(oneJoint);
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
		System.err.println("记录总数：" + count);
		StringBuilder stringBuilder = new StringBuilder();
		for (int k = 0; k < list.size(); k++) {
			if (list.get(k).substring(list.get(k).length() - 1, list.get(k).length()).equals(",")) {
				stringBuilder.append("(" + list.get(k).substring(0, list.get(k).length() - 1) + "),");
			} else {
				stringBuilder.append("(" + list.get(k) + "),");
			}
		}
		String asd = stringBuilder.substring(0, stringBuilder.length() - 1);
		Q qInsert = new Q(
				" INSERT INTO eao_engineer(UserName,Applicant,RegNumber,ProfessionalNumber,RegisterClass,StartTime,EndTime,IntegrityMessage) VALUES "
						+ asd);
		trans.add(qInsert);
		if (trans.commit()) {
			System.out.println("添加成功");
			return "添加成功";
		} else {
			System.out.println("添加失败");
			return "添加失败";
		}
	}

	public static String strToDate(Date str) throws ParseException {
		if (null == str) {
			return "";
		}
		SimpleDateFormat date = new SimpleDateFormat("yyyy/MM/dd");

		return date.format(str);
	}

}
